<?php
	//----------------------------------------------------------
	// BeSpoke API - User Info Node
	// Author  : Lance Kuo
	// Created : 2012/07/25
	// Version : V1.0.0.0
	// Note    : BeSpoke API function for User Account Info Node request/response XML formate Data
	// History : 2012/07/25 Created by Lance Version 1, UnTest
	//----------------------------------------------------------

//	$xml = simplexml_load_file('php://input');
	$xml = simplexml_load_file('userInfo.xml');
	
	//$array = $xml->attributes();
	
	foreach( $xml->attributes( ) as $attr)
	{
		$AttrArray[] = $attr;
	}
	
	$UserName = $AttrArray[0];
	$Password= $AttrArray[1];
	$Plaintext = $AttrArray[2];
	
    //Link to MySql
    include("mysql_connect.php");

	if( strlen($UserName) != 0)
	{
    	$strSqlCommand = "SELECT idUSER, Name, Level, Organization, Default_Channel
							FROM user
							WHERE Email = '".$UserName."' 
							AND Password = '".$Password."' ";
		
		$result = mysql_query($strSqlCommand);
		$rowUserInfo = @mysql_fetch_array($result);
		
		echo 'AccountInfo UserName="'.$UserName.'" Level="'.$rowUserInfo['Level'].'" ';
		echo 'Name="'.$rowUserInfo['Name'].'" Organization="'.$rowUserInfo['Organization'].'"<br/>';

		//---------Level 1 Share Group-------------------------------------------------------------------------------//		    	
		$strSqlCommand = "SELECT idGROUP, Name
							FROM spot_user_group tbSpot, sharegroup tbGroup
							WHERE tbSpot.USER_idUSER = ".$rowUserInfo['idUSER']." 
							AND tbSpot.GROUP_idGROUP = tbGroup.idGROUP ";
		
							
		$resultGroup = mysql_query($strSqlCommand);
		if( @mysql_num_rows($resultGroup) > 0 )
		{
			echo "ShareGroup<br/>";
			//---------Level 2 Group(List) in ShareGroup--------------------------------------------------------------//		
			while($rowGroup = @mysql_fetch_array($resultGroup))
			{
				echo 'Group ID="'.$rowGroup['idGROUP'].'" Name="'.$rowGroup['Name'].'"<br/>';
				
				//---------Level 3 User(List) in Gruop--------------------------------------------------------------------//	
				$strSqlCommand = "SELECT Name, Email
									FROM spot_user_group tbSpot, user tbUser
									WHERE tbSpot.GROUP_idGROUP = ".$rowGroup['idGROUP']." 
									AND tbSpot.USER_idUSER = tbUser.idUSER 
									AND tbUser.idUSER != ".$rowUserInfo['idUSER'];
							
				$resultUser = mysql_query($strSqlCommand);
				while($rowUser = @mysql_fetch_array($resultUser))
				{
					echo 'User Name="'.$rowUser['Name'].'" Email="'.$rowUser['Email'].'"<br/>';
				}
				// -----------------End of User List in Group---------------------//

				
				echo '/Group<br/>';
			}
			// -----------------End of Group List in ShareGroup---------------------//
			echo "/ShareGroup<br/>";
			
		}
		// -----------------End ShareGroup---------------------//

		//---------Level 1 MyBoard-------------------------------------------------------------------------------//
		$strSqlCommand = "SELECT GROUP_idGROUP, STORYBOARD_idSTORYBOARD, Name
							FROM spot_sb_group tbSpot, storyboard tbBoard
							WHERE tbSpot.USER_idUSER = ".$rowUserInfo['idUSER']." 
							AND tbSpot.STORYBOARD_idSTORYBOARD = tbBoard.idSTORYBOARD 
							GROUP BY STORYBOARD_idSTORYBOARD";
							
		$resultBoard = mysql_query($strSqlCommand);
		
		if( @mysql_num_rows($resultBoard) > 0 )
		{
			echo 'MyBoard MediaDownloadPath=""<br/>';
			//---------Level 2 Board List-------------------------------------------------------------------------------//
			while($rowBoard = @mysql_fetch_array($resultBoard))
			{
				$strBord = 'Board ID="'.$rowBoard['STORYBOARD_idSTORYBOARD'].'" Name="'.$rowBoard['Name'].'" ShareGroup="';
				
				$strSqlCommand = "SELECT GROUP_idGROUP FROM spot_sb_group
							WHERE USER_idUSER = ".$rowUserInfo['idUSER']." 
							AND STORYBOARD_idSTORYBOARD = ".$rowBoard['STORYBOARD_idSTORYBOARD'];
				$resultSGID = mysql_query($strSqlCommand);
				while($rowSGID = @mysql_fetch_array($resultSGID))
				{
					$strBord .= $rowSGID['GROUP_idGROUP'].',';
				}
				
				echo substr($strBord, 0, -1) . '"<br/>';

				//---------Level 3 Media List-------------------------------------------------------------------------------//

				$strSqlCommand = "SELECT idMEDIA, MEDIA_TYPE_idMEDIA_TYPE, WebLink FROM SPOT_USER_SB_M tbSpot, MEDIA tbMedia
								  WHERE tbSpot.STORYBOARD_idSTORYBOARD = ".$rowBoard['STORYBOARD_idSTORYBOARD']."
								  AND tbSpot.USER_idUSER = ".$rowUserInfo['idUSER']."
								  AND tbSpot.MEDIA_idMEDIA = tbMedia.idMEDIA";
//				echo $strSqlCommand."<br/>";

				// Get Media Item List
				$resultMediaList = mysql_query($strSqlCommand);
				while($rowMediaList = @mysql_fetch_array($resultMediaList))
				{
					// Get Comment Count
					$strSqlCommand = "SELECT COUNT(*) FROM COMMENTS WHERE MEDIA_idMEDIA = ".$rowMediaList['idMEDIA'];
					$resultComment = mysql_query($strSqlCommand);	
					$nCommentCount = @mysql_fetch_array($resultComment);
			
					// Get Media Type
					$strSqlCommand = "SELECT Name FROM MEDIA_TYPE WHERE idMEDIA_TYPE = ".$rowMediaList['MEDIA_TYPE_idMEDIA_TYPE'];
					$resultMediaType = mysql_query($strSqlCommand);	
					$rowMediaType = @mysql_fetch_array($resultMediaType);
					
					echo 'Media ID='.$rowMediaList['idMEDIA'].' MediaType='.$rowMediaType['Name'].'
							CommentCount='.$nCommentCount[0].' WebLink='.$rowMediaList['WebLink'].'<br/>';
		
					// Get Text Data
					$strSqlCommand = "SELECT * FROM TEXT WHERE MEDIA_idMEDIA = ".$rowMediaList['idMEDIA'];
					$resultText = mysql_query($strSqlCommand);	
					while($rowText = mysql_fetch_array($resultText))
					{
						echo 'Text ID="'.$rowText['idText'].'" Title="'.$rowText['Title'].'" Description="'.$rowText['Description'].'" /<br/>';
					}
					// Get Image Data
					$strSqlCommand = "SELECT * FROM IMAGE WHERE MEDIA_idMEDIA = ".$rowMediaList['idMEDIA'];
					$resultImage = mysql_query($strSqlCommand);	
					while($rowImage = mysql_fetch_array($resultImage))
					{
						echo 'Image ID="'.$rowImage['idIMAGE'].'" Type="'.$rowImage['Type'].'" FileName="'.$rowImage['FileName'].'" /<br/>';
					}
		
					// Get Video Data
					$strSqlCommand = "SELECT * FROM Video WHERE MEDIA_idMEDIA = ".$rowMediaList['idMEDIA'];
					$resultVideo = mysql_query($strSqlCommand);	
					while($rowVideo = mysql_fetch_array($resultVideo))
					{
						echo 'Video ID="'.$rowVideo['idVideo'].'" Type="'.$rowVideo['Type'].'" FileName="'.$rowVideo['FileName'].'" /<br/>';
					}
		
					echo "/Media<br/>";
   				 }
	
				//---------End of Media List-------------------------------------------------------------------------------//				
				echo '/Board<br/>';
			}
			//---------End of Board List-------------------------------------------------------------------------------//

			echo "/MyBoard<br/>";
		}
		// -----------------End of MyBoard---------------------//

		// Initialization
		echo "Initialization<br/>";

			// Categories
			echo "Categories<br/>";

				$strSqlCommand = "SELECT Name 
									FROM CATEGORY 
									WHERE CHANNELS_idCHANNELS = ".$rowUserInfo['Default_Channel'];
				$resultCt = mysql_query($strSqlCommand);	
				while($rowCt = mysql_fetch_array($resultCt))
				{
					echo 'Category Name="'. $rowCt['Name'] .'" /<br/>';
				}
			
			echo "/Categories<br/>";
			// End of Categories

			// Colors
			echo "Colors<br/>";
				$strSqlCommand = "SELECT Name, HexValue
									FROM COLOR 
									WHERE CHANNELS_idCHANNELS = ".$rowUserInfo['Default_Channel'];
				$resultCt = mysql_query($strSqlCommand);	
				while($rowCt = mysql_fetch_array($resultCt))
				{
					echo 'Color Name="'. $rowCt['Name'] .'" HexValue="'.$rowCt['HexValue'].'" /<br/>';
				}
			
			echo "/Colors<br/>";
			// End of Colors

			// Tags
			echo "Tags<br/>";
				$strSqlCommand = "SELECT Name, Priority
									FROM TAGS 
									WHERE CHANNELS_idCHANNELS = ".$rowUserInfo['Default_Channel'];
				$resultCt = mysql_query($strSqlCommand);	
				while($rowCt = mysql_fetch_array($resultCt))
				{
					echo 'Tags Name="'. $rowCt['Name'] .'" Size="'.$rowCt['Priority'].'" /<br/>';
				}
			
			echo "/Tags<br/>";
			// End of Categories
	
		echo "/Initialization<br/>";
		// End of Initialization
		echo '/AccountInfo<br/>';		

	}
	else
	{
		echo '<AccountInfo UserName="" Level="" Name="" Organization="">';	
		
		// Initialization
		echo "<Initialization>";

			// Categories
			echo "<Categories>";

				$strSqlCommand = "SELECT Name 
									FROM CATEGORY 
									WHERE CHANNELS_idCHANNELS = 0";//.$rowUserInfo['Default_Channel'];
				$resultCt = mysql_query($strSqlCommand);	
				while($rowCt = mysql_fetch_array($resultCt))
				{
					echo '<Category Name="'. $rowCt['Name'] .'" />';
				}
			
			echo "</Categories>";
			// End of Categories

			// Colors
			echo "<Colors>";
				$strSqlCommand = "SELECT Name, HexValue
									FROM COLOR 
									WHERE CHANNELS_idCHANNELS = 0";//.$rowUserInfo['Default_Channel'];
				$resultCt = mysql_query($strSqlCommand);	
				while($rowCt = mysql_fetch_array($resultCt))
				{
					echo '<Color Name="'. $rowCt['Name'] .'" HexValue="'.$rowCt['HexValue'].'" />';
				}
			
			echo "</Colors>";
			// End of Colors

			// Tags
			echo "<Tags>";
				$strSqlCommand = "SELECT Name, Priority
									FROM TAGS 
									WHERE CHANNELS_idCHANNELS = 0";//.$rowUserInfo['Default_Channel'];
				$resultCt = mysql_query($strSqlCommand);	
				while($rowCt = mysql_fetch_array($resultCt))
				{
					echo '<Tags Name="'. $rowCt['Name'] .'" Size="'.$rowCt['Priority'].'" />';
				}
			
			echo "</Tags>";
			// End of Categories
	
		echo "</Initialization>";
		// End of Initialization
			
		echo '</AccountInfo>';
	}
	

?>